#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
-- 意向明细宽表插入语句
INSERT INTO edu_online_dwb.dwb_relationship_detail PARTITION (dt)
SELECT
    fcr.id,
    fcr.create_date_time,
    fcr.origin_type,
    fcr.origin_channel ,
    fcc.clue_state,
    dc.area,
    disu.id,
    disu.name,
    disc.id,
    disc.name,
    dsd.id,
    dsd.name,
    substring(fcr.create_date_time,1,10) AS dt
FROM edu_online_dwd.fact_customer_relationship fcr
LEFT JOIN edu_online_dwd.fact_customer_clue fcc ON fcr.id = fcc.customer_relationship_id
LEFT JOIN edu_online_dwd.dim_customer dc ON fcr.id = dc.customer_relationship_id
LEFT JOIN edu_online_dwd.dim_itcast_subject disu ON fcr.itcast_subject_id = disu.id
LEFT JOIN edu_online_dwd.dim_itcast_school disc ON fcr.itcast_school_id = disc.id
LEFT JOIN edu_online_dwd.dim_employee de ON fcr.creator = de.id
LEFT JOIN edu_online_dwd.dim_scrm_department dsd ON de.tdepart_id = dsd.id;


-- 线索明细宽表插入语句
INSERT INTO edu_online_dwb.dwb_clue_detail PARTITION (dt)
SELECT
    fcc.id,
    fcc.create_date_time,
    fcc.clue_state,
    fcc.origin_type,
    dca.appeal_status,
    substring(fcc.create_date_time,1,10) AS dt
FROM edu_online_dwd.fact_customer_clue fcc
LEFT JOIN edu_online_dwd.dim_customer_appeal dca
ON fcc.customer_relationship_id = dca.customer_relationship_first_id;
"